﻿Public Class AdminProvidersFormV2

    Dim RelationshipDV As DataView
    Dim FundingStreamDV As DataView
    Dim ContractDV As DataView
    Dim PreDV As DataView

    Dim OriginalUPIN As Integer
    Dim OriginalUAI As String

    Private Sub BudgetButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProviderButton.Click
        ProviderMenu.Show(ProviderButton, New System.Drawing.Point(0, ProviderButton.Height))
    End Sub

    Private Sub AdminProvidersFormV2_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        ProviderBox.zShowNonRelationships = True

        ProviderBox.zConnectionString = StoreConnect
    End Sub

    Private Sub ResetProviderDetails()
        UPINBox.Text = ""
        NameBox.Text = ""

        If TypeBox.Items.Count <> 0 Then
            TypeBox.SelectedIndex = 0
        End If

        If SubTypeBox.Items.Count <> 0 Then
            SubTypeBox.SelectedIndex = 0
        End If

        DateOpenedBox.Value = Now

        RelationshipRepeater.ItemCount = 0

        URNBox.Text = ""
        EstabBox.Text = ""

    End Sub

    Private Sub ResetFundingStreams()
        FSPeriodBox.zConnectionString = StoreConnect
        FSPeriodBox.zSelectedPeriod_ID = CurrentPeriodID

        FSRepeater.ItemCount = 0

    End Sub

    Private Sub ResetContractInfo()
        CCMPeriodBox.zConnectionString = StoreConnect
        CCMPeriodBox.zSelectedPeriod_ID = CurrentPeriodID

        CCMRepeater.ItemCount = 0

    End Sub

    Private Sub ResetAcademyInfo()
        UAIBox.Text = ""

        If AcademyType.Items.Count <> 0 Then
            AcademyType.SelectedIndex = 0
        End If

        If AcadFundingTypeBox.Items.Count <> 0 Then
            AcadFundingTypeBox.SelectedIndex = 0
        End If

        If FundingMethodBox.Items.Count <> 0 Then
            FundingMethodBox.SelectedIndex = 0
        End If


        MultiSponsorCheck.Checked = False

        MultiSponsorNameBox.Text = ""

        FederationCheck.Checked = False

        GrowingSchoolCheck.Checked = False

    End Sub

    Private Sub ResetPreInfo()
        PreRepeater.ItemCount = 0
    End Sub

    Private Sub ResetAll()
        ResetProviderDetails()
        ResetAcademyInfo()
        ResetContractInfo()
        ResetFundingStreams()
        ResetPreInfo()

    End Sub

    Private Sub AddProvider()
        ProviderBox.TheTextBox.Text = ""
        ProviderBox.ClearSelected()
        AcademyPanel.Enabled = True
        ResetAll()
        LoadTypeBox()
        LoadSubTypeBox()
        LoadAcadInfo()
        ResetViews()
        MainPanel.Visible = True
        OriginalUAI = ""
        OriginalUPIN = Nothing



    End Sub

    Private Sub ResetViews()
        'relationship
        RelationshipDV = Nothing
        RelationshipDV = New DataView(New DataTable("relationships"))

        With RelationshipDV
            .Table.Columns.Add("RID", GetType(String))
            .Table.Columns.Add("LA_Code", GetType(String))
            .Table.Columns.Add("Lead", GetType(Integer))
            .Table.Columns.Add("LA_Name", GetType(String))
            .Table.Columns.Add("Territory_Code", GetType(String))
            .Table.Columns.Add("Mode", GetType(String))
        End With

        'fundingstream
        FundingStreamDV = Nothing
        FundingStreamDV = LoadDataView("select Budget_ID, Budget_Description, ' ' as Checked, Period_ID, ' ' as Original_Check from Budgets, Periods")
        FundingStreamDV.RowFilter = "Period_ID = '" & FSPeriodBox.zSelectedPeriod_ID & "'"

        FSRepeater.ItemCount = FundingStreamDV.Count

        FSRepeater.BeginResetItemTemplate()
        FSRepeater.EndResetItemTemplate()

        'contract codes
        ContractDV = Nothing
        ContractDV = New DataView(New DataTable("contracts"))

        With ContractDV
            .Table.Columns.Add("Period_ID", GetType(Integer))
            .Table.Columns.Add("UPIN", GetType(Integer))
            .Table.Columns.Add("CCM_Funding_Stream", GetType(String))
            .Table.Columns.Add("Description", GetType(String))
            .Table.Columns.Add("Contract_Number", GetType(String))
            .Table.Columns.Add("Parent_Contract_Number", GetType(String))
            .Table.Columns.Add("Mode", GetType(String))
        End With

        'pre data
        PreDV = Nothing
        PreDV = New DataView(New DataTable("predata"))

        With PreDV
            .Table.Columns.Add("Pre_UPIN", GetType(Integer))
            .Table.Columns.Add("Provider_Name", GetType(String))
            .Table.Columns.Add("Mode", GetType(String))
        End With

    End Sub

    Private Sub AddToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddToolStripMenuItem.Click
        AddProvider()


    End Sub

    Private Sub ProviderBox_ProviderSelected(ByVal e As TheStore.ProviderSearchBoxCtrl.SelectedProviderClass) Handles ProviderBox.ProviderSelected
        LoadProvider()

    End Sub

    Private Sub LoadTypeBox()
        LoadComboBox(TypeBox, "select * from Provider_Types", "Provider_Type_Code", "Provider_Type_Description")
    End Sub

    Private Sub LoadSubTypeBox()
        Try
            LoadComboBox(SubTypeBox, "select * from Provider_Subtypes where Provider_Type_Code = '" & TypeBox.SelectedValue.ToString & "'", "Provider_Subtype_Code", "Provider_Subtype_Description")
        Catch ex As Exception

        End Try

    End Sub

    Private Sub LoadProvider()
        ResetAll()

        Dim DV As DataView
        Dim SQL As String

        SQL = "Select * from Providers where UPIN = " & ProviderBox.zSelectedProvider.UPIN
        DV = LoadDataView(SQL)

        If DV.Count = 0 Then
            Exit Sub
        End If

        'Provider Details
        UPINBox.Text = DV(0)("UPIN").ToString
        OriginalUPIN = DV(0)("UPIN")
        NameBox.Text = DV(0)("Provider_Name").ToString
        LoadTypeBox()

        If DV(0)("Provider_Type_Code").ToString = "Blank" Then
            TypeBox.SelectedValue = "Furth"
            LoadSubTypeBox()
            SubTypeBox.SelectedIndex = 0
        Else
            TypeBox.SelectedValue = DV(0)("Provider_Type_Code").ToString
            LoadSubTypeBox()
            SubTypeBox.SelectedValue = DV(0)("Provider_Subtype_Code").ToString
        End If

        
        DateOpenedBox.Value = IIf(IsDBNull(DV(0)("Date_Opened")), Now, DV(0)("Date_Opened"))
        URNBox.Text = DV(0)("URN").ToString
        EstabBox.Text = DV(0)("Establishment_Number").ToString

        'relationships
        LoadRelationships()

        'FS
        LoadFundingStreams()

        'Contract Information
        LoadContractInfo()

        'academy
        ShowHideAcademyInfo()
        LoadAcadInfo()

        'predata
        LoadPreData()

        MainPanel.Visible = True
    End Sub

    Private Sub LoadContractInfo()
        Dim SQL As String

        SQL = "select Period_ID, UPIN, cn.CCM_Funding_Stream, Description, Contract_Number, Parent_Contract_Number, 'O' as Mode  from CCM_Provider_Contract_Numbers cn inner join CCM_Funding_Streams fs on (cn.CCM_Funding_Stream = fs.CCM_Funding_Stream) where UPIN = " & ProviderBox.zSelectedProvider.UPIN

        ContractDV = LoadDataView(SQL)

        ContractDV.RowFilter = "Period_ID = '" & CCMPeriodBox.zSelectedPeriod_ID.ToString & "'"

        CCMRepeater.ItemCount = ContractDV.Count

        CCMRepeater.BeginResetItemTemplate()
        CCMRepeater.EndResetItemTemplate()

    End Sub

    Private Sub LoadRelationships()

        Dim SQL As String

        SQL = "select RID, r.LA_Code, isnull(Lead,0) as Lead, l.LA_Name, j.Territory_Code ,'O' as Mode  from " & _
                    "Relationships r inner join Local_Authorities l on (r.LA_Code = l.LA_Code) " & _
                    "inner join TerritoryJoin j on (l.Region_Code = j.Region_Code) " & _
                    "where UPIN = " & ProviderBox.zSelectedProvider.UPIN & " order by isnull(Lead,0) desc, LA_Name asc"
        RelationshipDV = LoadDataView(SQL)

        RelationshipRepeater.ItemCount = RelationshipDV.Count

        RelationshipRepeater.BeginResetItemTemplate()
        RelationshipRepeater.EndResetItemTemplate()


    End Sub

    Private Sub LoadAcadInfo()
        LoadAcadFundingMethodBox()

        Dim SQL As String
        Dim DV As DataView

        SQL = "select * from Acad_Additional_Provider_Info where UAI = '" & ProviderBox.zSelectedProvider.UAI & "'"
        DV = LoadDataView(SQL)

        If DV.Count <> 0 Then

            Dim AcadType As Object

            AcadType = LoadDataView("Select Converter from Providers where UPIN = " & ProviderBox.zSelectedProvider.UPIN)(0)("Converter")

            If IsDBNull(AcadType) Then
                AcadType = 0
            End If

            UAIBox.Text = ProviderBox.zSelectedProvider.UAI
            OriginalUAI = ProviderBox.zSelectedProvider.UAI
            AcademyType.SelectedIndex = AcadType
            AcadFundingTypeBox.SelectedIndex = DV(0)("Funding_Agreement_Type")
            FundingMethodBox.SelectedValue = DV(0)("Funding_ID")

            If DV(0)("Multi_Sponsor") = 0 Then
                MultiSponsorCheck.Checked = False
            Else
                MultiSponsorCheck.Checked = True
            End If

            MultiSponsorNameBox.Text = DV(0)("Multi_Sponsor_Name").ToString

            If DV(0)("Federation") = 0 Then
                FederationCheck.Checked = False
            Else
                FederationCheck.Checked = True
            End If

            If DV(0)("Growing_School") = 0 Then
                GrowingSchoolCheck.Checked = False
            Else
                GrowingSchoolCheck.Checked = True
            End If

        Else
            ResetAcademyInfo()
        End If



    End Sub

    Private Sub LoadAcadFundingMethodBox()
        LoadComboBox(FundingMethodBox, "select * from Acad_Funding_Methods order by Funding_name", "Funding_ID", "Funding_Name")

    End Sub

    Private Sub TypeBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TypeBox.SelectedIndexChanged
        LoadSubTypeBox()

        ShowHideAcademyInfo()


    End Sub

    Private Sub ShowHideAcademyInfo()
        If TypeBox.SelectedValue.ToString = "Acade" Then
            AcademyPanel.Enabled = True

        Else
            AcademyPanel.Enabled = False
        End If
    End Sub

    Private Sub RelationshipRepeater_ItemValueNeeded(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemValueEventArgs) Handles RelationshipRepeater.ItemValueNeeded

        Dim i As Integer = e.ItemIndex

        If i >= 0 Then


            Select Case e.Control.Name
                Case "LALabel"

                    e.Value = RelationshipDV(i)("LA_Name")
                Case "LeadImage"
                    If RelationshipDV(i)("Lead").ToString = "1" Then
                        e.Control.Visible = True
                    Else
                        e.Control.Visible = False
                    End If
                Case Else

            End Select


        End If

    End Sub

    Private Sub LoadFundingStreams()
        Dim SQL As String

        SQL = "select Budget_ID, Budget_Description, ' ' as Checked, Period_ID, ' ' as Original_Check from Budgets b, Periods p " & _
                "where b.Budget_ID + cast(p.Period_ID as nvarchar(10)) not in  " & _
                "(Select pfs.Budget_ID + cast(pfs.Period_ID as nvarchar(10)) from Provider_Funding_Streams pfs where UPIN = " & ProviderBox.zSelectedProvider.UPIN & ") " & _
                "union " & _
                "select b.Budget_ID, b.Budget_Description, 'X' as Checked,   " & _
                "period_id, 'X' as Original_Check from Budgets b inner join Provider_Funding_Streams p on (b.Budget_ID = p.Budget_ID and p.UPIN = " & ProviderBox.zSelectedProvider.UPIN & ") "

        FundingStreamDV = LoadDataView(SQL)

        FundingStreamDV.RowFilter = "Period_ID = '" & FSPeriodBox.zSelectedPeriod_ID & "'"

        FSRepeater.ItemCount = FundingStreamDV.Count

        FSRepeater.BeginResetItemTemplate()
        FSRepeater.EndResetItemTemplate()

    End Sub

    Private Sub FSRepeater_ItemValueNeeded(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemValueEventArgs) Handles FSRepeater.ItemValueNeeded
        Dim i As Integer

        i = e.ItemIndex

        If i >= 0 Then
            Select Case e.Control.Name
                Case "FSCheckBox"
                    e.Value = FundingStreamDV(i)("Checked")
                Case "FSNameLabel"
                    e.Value = FundingStreamDV(i)("Budget_Description")
            End Select
        End If
    End Sub

    Private Sub FSPeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FSPeriodBox.SelectedIndexChanged
        Try
            FundingStreamDV.RowFilter = "Period_ID = '" & FSPeriodBox.zSelectedPeriod_ID & "'"

            FSRepeater.ItemCount = FundingStreamDV.Count

            FSRepeater.BeginResetItemTemplate()
            FSRepeater.EndResetItemTemplate()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub CCMRepeater_ItemValueNeeded(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemValueEventArgs) Handles CCMRepeater.ItemValueNeeded
        Dim i As Integer

        i = e.ItemIndex

        If i >= 0 Then

            Select Case e.Control.Name
                Case "CCMFundingStream"
                    e.Value = ContractDV(i)("Description").ToString
                Case "CCMNumber"
                    e.Value = ContractDV(i)("Contract_Number").ToString
                Case "CCMParentNumber"
                    e.Value = ContractDV(i)("Parent_Contract_Number").ToString
                Case Else

            End Select

        End If
    End Sub

    Private Sub CCMPeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CCMPeriodBox.SelectedIndexChanged
        Try
            ContractDV.RowFilter = "Period_ID = '" & CCMPeriodBox.zSelectedPeriod_ID.ToString & "'"

            CCMRepeater.ItemCount = ContractDV.Count

            CCMRepeater.BeginResetItemTemplate()
            CCMRepeater.EndResetItemTemplate()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub LoadPreData()
        Dim SQL As String

        SQL = "select Pre_UPIN, p.Provider_Name, 'O' as Mode   from Predecessor_Providers pp inner join Providers p on (pp.Pre_UPIN = p.UPIN and pp.UPIN = " & ProviderBox.zSelectedProvider.UPIN & ")"

        PreDV = LoadDataView(SQL)

        PreRepeater.ItemCount = PreDV.Count

        PreRepeater.BeginResetItemTemplate()
        PreRepeater.EndResetItemTemplate()

    End Sub

    Private Sub PreRepeater_ItemValueNeeded(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemValueEventArgs) Handles PreRepeater.ItemValueNeeded
        Dim i As Integer

        i = e.ItemIndex

        If i >= 0 Then

            Select Case e.Control.Name
                Case "UPINLabel"
                    e.Value = PreDV(i)("Pre_UPIN").ToString
                Case "ProviderLabel"
                    e.Value = PreDV(i)("Provider_Name").ToString
                Case Else

            End Select


        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'Delete relationship

        If MessageBox.Show("Are you sure you want to delete this relationship?", "Delete relationship?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim i As Integer = RelationshipRepeater.CurrentItemIndex

        If i >= 0 Then
            RelationshipDV(i)("Mode") = "D"

            RelationshipDV.RowFilter = "Mode <> 'D'"

            RelationshipRepeater.ItemCount = RelationshipDV.Count

        End If

    End Sub

    Private Sub RefreshRelationshipRepeater()

        RelationshipRepeater.ItemCount = RelationshipDV.Count
        RelationshipRepeater.BeginResetItemTemplate()
        RelationshipRepeater.EndResetItemTemplate()

    End Sub

    Private Sub RefreshCCMContractRepeater()
        CCMRepeater.ItemCount = ContractDV.Count
        CCMRepeater.BeginResetItemTemplate()
        CCMRepeater.EndResetItemTemplate()

    End Sub

    Private Sub RefreshPreRepeater()
        PreRepeater.ItemCount = PreDV.Count
        PreRepeater.BeginResetItemTemplate()
        PreRepeater.EndResetItemTemplate()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'add relationship
        With RelationshipDialog
            .LoadTerritoryBox()
            .LoadLABox()

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                'first check if this one is already there
                Dim r As DataRowView
                For Each r In RelationshipDV
                    If r("LA_Code") = .LABox.SelectedValue.ToString Then
                        MessageBox.Show("This Local Authority already has a relationship with this provider", "Already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                Next


                Dim lead As Integer

                If .LeadCheck.Checked Then
                    lead = 1
                    'uncheck all other leads


                    For Each r In RelationshipDV
                        r("Lead") = 0
                    Next

                Else
                    lead = 0
                End If


                RelationshipDV.Table.Rows.Add("TBC", .LABox.SelectedValue.ToString, lead, DirectCast(.LABox.DataSource, DataView)(.LABox.SelectedIndex)("LA_Name"), .TerritoryBox.SelectedValue, "A")

                RefreshRelationshipRepeater()

            End If
        End With


    End Sub

    Private Function GenerateRID() As String
        Dim s As DateTime
        Dim n As DateTime
        Dim RID As String

        n = Now

        s = "1 January 2000"

        Dim i As TimeSpan

        i = n - s


        Dim dv As DataView

        RID = ""

        While RID = ""

            RID = Math.Round(i.TotalSeconds, 0)

            Dim l As Integer = Len(RID)

            If l < 10 Then

                Select Case l
                    Case 8
                        RID = "00" & RID
                    Case 9
                        RID = "0" & RID
                    Case Else

                End Select

                RID = Mid(CurrentUser.Name, 1, 1) & RID
            End If

          

            dv = LoadDataView("Select RID from Relationships where RID = '" & RID & "'")

            If dv.Count <> 0 Then
                RID = ""
            End If
        End While


        Return RID

    End Function

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'edit relationship

        Dim i As Integer

        i = RelationshipRepeater.CurrentItemIndex

        If i < 0 Then Exit Sub

        With RelationshipDialog
            .LoadTerritoryBox()
            .LoadLABox()

            .TerritoryBox.SelectedValue = RelationshipDV(i)("Territory_Code").ToString
            .LABox.SelectedValue = RelationshipDV(i)("LA_Code").ToString

            If RelationshipDV(i)("Lead") = 1 Then
                .LeadCheck.Checked = True
            Else
                .LeadCheck.Checked = False
            End If


            If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                'first check if this one is already there
                Dim c As Integer = -1
                Dim r As DataRowView
                For Each r In RelationshipDV
                    c = c + 1

                    If c <> i Then
                        If r("LA_Code") = .LABox.SelectedValue.ToString Then
                            MessageBox.Show("This Local Authority already has a relationship with this provider", "Already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            Exit Sub
                        End If
                    End If
                    
                Next

                Dim lead As Integer

                If .LeadCheck.Checked Then
                    lead = 1
                    'uncheck all other leads


                    For Each r In RelationshipDV
                        r("Lead") = 0
                    Next

                Else
                    lead = 0
                End If

                Dim currentMode As String
                currentMode = RelationshipDV(i)("Mode")

                If currentMode = "O" Then
                    currentMode = "E"
                End If

                RelationshipDV(i)("LA_Code") = .LABox.SelectedValue.ToString
                RelationshipDV(i)("Lead") = lead
                RelationshipDV(i)("LA_Name") = DirectCast(.LABox.DataSource, DataView)(.LABox.SelectedIndex)("LA_Name")
                RelationshipDV(i)("Mode") = currentMode

                RefreshRelationshipRepeater()

            End If
        End With

    End Sub

    Private Sub TheCheckClick(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim l As Label

        l = sender

        If l.Text = "X" Then
            l.Text = " "
        Else
            l.Text = "X"
        End If

    End Sub

    Private Sub FSRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles FSRepeater.DrawItem

        Dim l As Label

        l = TryCast(e.DataRepeaterItem.Controls("FSCheckBox"), Label)

        RemoveHandler l.Click, AddressOf TheCheckClick
        AddHandler l.Click, AddressOf TheCheckClick

    End Sub

    Private Sub FSRepeater_ItemValuePushed(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemValueEventArgs) Handles FSRepeater.ItemValuePushed
        Dim i As Integer = e.ItemIndex

        If (i >= 0) Then


            Select Case e.Control.Name

                Case "FSCheckBox"
                    FundingStreamDV(i)("Checked") = e.Value

            End Select


        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CCMEditButton.Click
        CCMEditMenu.Show(CCMEditButton, New System.Drawing.Point(0, CCMEditButton.Height))
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim i As Integer

        i = CCMRepeater.CurrentItemIndex

        If i < 0 Then Exit Sub

        If MessageBox.Show("Are you sure you want to delete this contract number?", "Delete contract number?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        ContractDV(i)("Mode") = "D"

        ContractDV.RowFilter = "Mode <> 'D'"
        RefreshCCMContractRepeater()


    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        With CCMContractsDialog
            .LoadFundingStreamBox()
            .CCMFundingStreamBox.Enabled = True

            .ContractNumberBox.Text = ""
            .Text = "Contract Number"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then

                'first check if this one is already there
                Dim r As DataRowView
                For Each r In ContractDV
                    If r("CCM_Funding_Stream") = .CCMFundingStreamBox.SelectedValue.ToString Then
                        MessageBox.Show("This funding stream is already listed.", "Already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                Next


                If Trim(.ContractNumberBox.Text) = "" Then
                    MessageBox.Show("You did not enter a contract number.", "No contract number", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If

                Dim description As String

                description = DirectCast(.CCMFundingStreamBox.DataSource, DataView)(.CCMFundingStreamBox.SelectedIndex)("Description").ToString

                ContractDV.Table.Rows.Add(CCMPeriodBox.zSelectedPeriod_ID, ProviderBox.zSelectedProvider.UPIN, .CCMFundingStreamBox.SelectedValue, description, .ContractNumberBox.Text, "", "A")

                RefreshCCMContractRepeater()


            End If

        End With
    End Sub

    Private Sub ToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem1.Click

        Dim i As Integer

        i = CCMRepeater.CurrentItemIndex

        If i < 0 Then Exit Sub

        With CCMContractsDialog
            .LoadFundingStreamBox()


            .CCMFundingStreamBox.SelectedValue = ContractDV(i)("CCM_Funding_Stream").ToString
            .CCMFundingStreamBox.Enabled = False

            .ContractNumberBox.Text = ContractDV(i)("Contract_Number").ToString
            .Text = "Contract Number"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then

                'first check if this one is already there
                Dim c As Integer = -1
                Dim r As DataRowView
                For Each r In ContractDV
                    c = c + 1

                    If c <> i Then
                        If r("CCM_Funding_Stream") = .CCMFundingStreamBox.SelectedValue.ToString Then
                            MessageBox.Show("This funding stream is already listed.", "Already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            Exit Sub
                        End If
                    End If
                    
                Next


                If Trim(.ContractNumberBox.Text) = "" Then
                    MessageBox.Show("You did not enter a contract number.", "No contract number", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If

                Dim description As String

                description = DirectCast(.CCMFundingStreamBox.DataSource, DataView)(.CCMFundingStreamBox.SelectedIndex)("Description").ToString

                Dim currentMode As String
                currentMode = ContractDV(i)("Mode")

                If currentMode = "O" Then
                    currentMode = "E"
                End If

                ContractDV(i)("Contract_Number") = .ContractNumberBox.Text
                ContractDV(i)("CCM_Funding_Stream") = .CCMFundingStreamBox.SelectedValue
                ContractDV(i)("Description") = description
                ContractDV(i)("Mode") = currentMode

                RefreshCCMContractRepeater()


            End If
        End With

    End Sub

    Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem2.Click
        Dim i As Integer

        i = CCMRepeater.CurrentItemIndex

        If i < 0 Then Exit Sub

        With CCMContractsDialog
            .LoadFundingStreamBox()

            .CCMFundingStreamBox.SelectedValue = ContractDV(i)("CCM_Funding_Stream").ToString
            .CCMFundingStreamBox.Enabled = False
            If IsDBNull(ContractDV(i)("Parent_Contract_Number").ToString) Then
                .ContractNumberBox.Text = ""
            Else
                .ContractNumberBox.Text = ContractDV(i)("Parent_Contract_Number").ToString
            End If


            .Text = "Parent Contract Number"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then

                'first check if this one is already there
                Dim c As Integer = -1
                Dim r As DataRowView
                For Each r In ContractDV
                    c = c + 1

                    If c <> i Then
                        If r("CCM_Funding_Stream") = .CCMFundingStreamBox.SelectedValue.ToString Then
                            MessageBox.Show("This funding stream is already listed.", "Already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            Exit Sub
                        End If
                    End If

                Next

                Dim description As String

                description = DirectCast(.CCMFundingStreamBox.DataSource, DataView)(.CCMFundingStreamBox.SelectedIndex)("Description").ToString

                Dim currentMode As String
                currentMode = ContractDV(i)("Mode")

                If currentMode = "O" Then
                    currentMode = "E"
                End If

                ContractDV(i)("Parent_Contract_Number") = .ContractNumberBox.Text
                ContractDV(i)("CCM_Funding_Stream") = .CCMFundingStreamBox.SelectedValue
                ContractDV(i)("Description") = description
                ContractDV(i)("Mode") = currentMode

                RefreshCCMContractRepeater()


            End If
        End With
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        'delete previous upin

        Dim i As Integer

        i = PreRepeater.CurrentItemIndex

        If i < 0 Then Exit Sub

        If MessageBox.Show("Are you sure you want to delete this record?", "Delete record?", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        PreDV(i)("Mode") = "D"
        PreDV.RowFilter = "Mode <> 'D'"
        RefreshPreRepeater()

    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        'add pre provider

        With Search_Form
            .WhoSentForMe = "AdminProvidersFormV2"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                'add

                Dim r As DataRowView

                For Each r In PreDV
                    If r("pre_UPIN") = .SearchResults_DataViewGrid.CurrentRow.Cells("UPIN").Value Then

                        MessageBox.Show("This provider is already in the list", "Provider already listed", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                Next

                PreDV.Table.Rows.Add(.SearchResults_DataViewGrid.CurrentRow.Cells("UPIN").Value, .SearchResults_DataViewGrid.CurrentRow.Cells("Provider").Value, "A")

                RefreshPreRepeater()
            End If

        End With
    End Sub

    Private Function ValidateAll() As Boolean
        'UPIN
        If Trim(UPINBox.Text) = "" Then
            MessageBox.Show("You have to enter a UPIN", "No UPIN entered", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return False
        End If

        Dim OUPIN As String

        If OriginalUPIN = Nothing Then
            OUPIN = ""
        Else
            OUPIN = OriginalUPIN
        End If

        If OUPIN = "" Or OUPIN <> UPINBox.Text Then
            'its new or it has changed so check if it does not exist
            Dim sql As String
            Dim dv As DataView

            sql = "select * from providers where UPIN = " & UPINBox.Text
            dv = LoadDataView(sql)

            If dv.Count <> 0 Then
                MessageBox.Show("The UPIN entered already exists, please enter another UPIN", "UPIN exists", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If

        End If

        'if it is an academy it must have an UAI
        If TypeBox.SelectedValue.ToString = "Acade" Then
            If UAIBox.Text = "" Then
                MessageBox.Show("You must enter an UAI for an Academy", "No UAI", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If

            If OriginalUAI = "" Or OriginalUAI <> UAIBox.Text Then
                'its new or it has changed so check if it does not exist
                Dim sql As String
                Dim dv As DataView

                sql = "select * from Acad_Additional_Provider_Info where UAI = '" & UAIBox.Text & "'"
                dv = LoadDataView(sql)

                If dv.Count <> 0 Then
                    MessageBox.Show("The UAI entered already exists, please enter another UAI", "UAI exists", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Return False
                End If
            End If
        End If

        If Trim(NameBox.Text) = "" Then
            MessageBox.Show("You must enter a name for the provider", "No name entered", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return False
        End If

        'URN and EStab check
        Dim i As Integer

        Try
            i = URNBox.Text
        Catch ex As Exception
            MessageBox.Show("The URN must be a number", "Incorrect URN", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return False
        End Try

        Try
            i = EstabBox.Text

        Catch ex As Exception
            MessageBox.Show("The Establishment Number must be a number", "Incorrect Establishment Number", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return False
        End Try

        Return True
    End Function

    Private Sub SaveAll()
        If Not ValidateAll() Then
            Exit Sub
        End If

        If OriginalUPIN = Nothing Then
            'its add
            'Dim sql As String


            'sql = "insert into Providers " & _
            '      "(UPIN, Provider_Name, Provider_Type_Code, URN, Establishment_Number, Converter, Converter_Date, Contract_Number, Parent_Contract_Number, UAI, Date_Opened, Provider_Subtype_Code) " & _
            '      "Values(@UPIN, @Provider_Name, @Provider_Type_Code, @URN, @Establishment_Number, @Converter, @Converter_Date, @Contract_Number, @Parent_Contract_Number, @UAI, @Date_Opened, @Provider_Subtype_Code)"

            'Dim p As New List(Of SqlClient.SqlParameter)

            'p.Clear()
            'p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Provider_Name", NameBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Provider_Type_Code", TypeBox.SelectedValue.ToString))
            'p.Add(New SqlClient.SqlParameter("@URN", URNBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Establishment_Number", EstabBox.Text))

            'If TypeBox.SelectedValue.ToString = "Acade" Then
            '    p.Add(New SqlClient.SqlParameter("@Converter", AcademyType.SelectedIndex))
            '    p.Add(New SqlClient.SqlParameter("@Converter_Date", DateOpenedBox.Value))
            '    p.Add(New SqlClient.SqlParameter("@UAI", UAIBox.Text))
            'Else
            '    p.Add(New SqlClient.SqlParameter("@Converter", DBNull.Value))
            '    p.Add(New SqlClient.SqlParameter("@Converter_Date", DBNull.Value))
            '    p.Add(New SqlClient.SqlParameter("@UAI", DBNull.Value))

            'End If

            'p.Add(New SqlClient.SqlParameter("@Contract_Number", DBNull.Value))
            'p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", DBNull.Value))
            'p.Add(New SqlClient.SqlParameter("@Date_Opened", DateOpenedBox.Value))
            'p.Add(New SqlClient.SqlParameter("@Provider_Subtype_Code", SubTypeBox.SelectedValue.ToString))

            'RunNonQueryParams(sql, p)

            'If TypeBox.SelectedValue.ToString = "Acade" Then
            '    'add additional academy info
            '    sql = "insert into Acad_Additional_Provider_Info " & _
            '        "(UAI, Funding_Agreement_Type, Federation, Multi_Sponsor, Multi_Sponsor_Name, Growing_School, Funding_ID) " & _
            '        "values (@UAI, @Funding_Agreement_Type, @Federation, @Multi_Sponsor, @Multi_Sponsor_Name, @Growing_School, @Funding_ID)"

            '    Dim checkval As Integer = 0

            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@UAI", UAIBox.Text))
            '    p.Add(New SqlClient.SqlParameter("@Funding_Agreement_Type", AcadFundingTypeBox.SelectedIndex))

            '    If FederationCheck.Checked = True Then checkval = 1 Else checkval = 0
            '    p.Add(New SqlClient.SqlParameter("@Federation", checkval))

            '    If MultiSponsorCheck.Checked = True Then checkval = 1 Else checkval = 0
            '    p.Add(New SqlClient.SqlParameter("@Multi_Sponsor", checkval))
            '    p.Add(New SqlClient.SqlParameter("@Multi_Sponsor_Name", MultiSponsorNameBox.Text))

            '    If GrowingSchoolCheck.Checked = True Then checkval = 1 Else checkval = 0
            '    p.Add(New SqlClient.SqlParameter("@Growing_School", checkval))

            '    p.Add(New SqlClient.SqlParameter("@Funding_ID", FundingMethodBox.SelectedValue))

            '    RunNonQueryParams(sql, p)
            'End If

            'Dim r As DataRowView

            ''Relationships
            'RelationshipDV.RowFilter = "Mode = 'A'"
            'For Each r In RelationshipDV
            '    sql = "insert into Relationships " & _
            '        "(RID, LA_Code, UPIN, Lead) " & _
            '        "values(@RID, @LA_Code, @UPIN, @Lead)"

            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@RID", GenerateRID))
            '    p.Add(New SqlClient.SqlParameter("@LA_Code", r("LA_Code")))
            '    p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '    p.Add(New SqlClient.SqlParameter("@Lead", r("Lead")))

            '    RunNonQueryParams(sql, p)
            'Next

            ''Funding Streams
            'FundingStreamDV.RowFilter = "Checked = 'X'"
            'For Each r In FundingStreamDV
            '    sql = "insert into Provider_Funding_Streams " & _
            '        "(Period_ID, UPIN, Budget_ID) " & _
            '        "values(@Period_ID, @UPIN, @Budget_ID)"

            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
            '    p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '    p.Add(New SqlClient.SqlParameter("@Budget_ID", r("Budget_ID")))

            '    RunNonQueryParams(sql, p)
            'Next

            ''Contract Information
            'ContractDV.RowFilter = "Mode = 'A'"
            'For Each r In ContractDV
            '    sql = "insert into CCM_Provider_Contract_Numbers " & _
            '        "(Period_ID, UPIN, CCM_Funding_Stream, Contract_Number, Parent_Contract_Number) " & _
            '        "values(@Period_ID, @UPIN, @CCM_Funding_Stream, @Contract_Number, @Parent_Contract_Number)"

            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
            '    p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '    p.Add(New SqlClient.SqlParameter("@CCM_Funding_Stream", r("CCM_Funding_Stream")))
            '    p.Add(New SqlClient.SqlParameter("@Contract_Number", r("Contract_Number")))

            '    Dim pc As Object

            '    If Not IsDBNull(r("Parent_Contract_Number")) Then
            '        If Trim(r("Parent_Contract_Number")) = "" Then
            '            pc = DBNull.Value
            '        Else
            '            pc = r("Parent_Contract_Number")
            '        End If
            '    Else
            '        pc = DBNull.Value
            '    End If

            '    p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", pc))

            '    RunNonQueryParams(sql, p)
            'Next

            ''pre's
            'PreDV.RowFilter = "Mode = 'A'"
            'For Each r In PreDV
            '    sql = "insert into Predecessor_Providers (UPIN, Pre_UPIN) values(@UPIN, @Pre_UPIN)"
            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@UPIN", ProviderBox.zSelectedProvider.UPIN))
            '    p.Add(New SqlClient.SqlParameter("@Pre_UPIN", r("Pre_UPIN")))

            '    RunNonQueryParams(sql, p)
            'Next

            'MessageBox.Show("The provider has been added", "Added new provder", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            'its edit
            Dim sql As String


            'sql = "Update Providers set " & _
            '      "UPIN = @UPIN, Provider_Name = @Provider_Name, Provider_Type_Code = @Provider_Type_Code, URN = @URN, Establishment_Number = @Establishment_Number, Converter = @Converter, Converter_Date = @Converter_Date, Contract_Number = @Contract_Number, Parent_Contract_Number = @Parent_Contract_Number, UAI = @UAI, Date_Opened = @Date_Opened, Provider_Subtype_Code = @Provider_Subtype_Code " & _
            '      "where UPIN = @Original_Upin"

            Dim p As New List(Of SqlClient.SqlParameter)

            p.Clear()
            'p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Provider_Name", NameBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Provider_Type_Code", TypeBox.SelectedValue.ToString))
            'p.Add(New SqlClient.SqlParameter("@URN", URNBox.Text))
            'p.Add(New SqlClient.SqlParameter("@Establishment_Number", EstabBox.Text))

            'If TypeBox.SelectedValue.ToString = "Acade" Then
            '    p.Add(New SqlClient.SqlParameter("@Converter", AcademyType.SelectedIndex))
            '    p.Add(New SqlClient.SqlParameter("@Converter_Date", DateOpenedBox.Value))
            '    p.Add(New SqlClient.SqlParameter("@UAI", UAIBox.Text))
            'Else
            '    p.Add(New SqlClient.SqlParameter("@Converter", DBNull.Value))
            '    p.Add(New SqlClient.SqlParameter("@Converter_Date", DBNull.Value))
            '    p.Add(New SqlClient.SqlParameter("@UAI", DBNull.Value))

            'End If

            'p.Add(New SqlClient.SqlParameter("@Contract_Number", DBNull.Value))
            'p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", DBNull.Value))
            'p.Add(New SqlClient.SqlParameter("@Date_Opened", DateOpenedBox.Value))
            'p.Add(New SqlClient.SqlParameter("@Provider_Subtype_Code", SubTypeBox.SelectedValue.ToString))
            'p.Add(New SqlClient.SqlParameter("@Original_Upin", OriginalUPIN))

            'RunNonQueryParams(sql, p)



            'If TypeBox.SelectedValue.ToString = "Acade" Then
            '    If OriginalUAI = "" Then
            '        'insert
            '        'add additional academy info
            '        sql = "insert into Acad_Additional_Provider_Info " & _
            '            "(UAI, Funding_Agreement_Type, Federation, Multi_Sponsor, Multi_Sponsor_Name, Growing_School, Funding_ID) " & _
            '            "values (@UAI, @Funding_Agreement_Type, @Federation, @Multi_Sponsor, @Multi_Sponsor_Name, @Growing_School, @Funding_ID)"

            '        Dim checkval As Integer = 0

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@UAI", UAIBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Funding_Agreement_Type", AcadFundingTypeBox.SelectedIndex))

            '        If FederationCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Federation", checkval))

            '        If MultiSponsorCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Multi_Sponsor", checkval))
            '        p.Add(New SqlClient.SqlParameter("@Multi_Sponsor_Name", MultiSponsorNameBox.Text))

            '        If GrowingSchoolCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Growing_School", checkval))

            '        p.Add(New SqlClient.SqlParameter("@Funding_ID", FundingMethodBox.SelectedValue))

            '        RunNonQueryParams(sql, p)
            '    Else
            '        'edit
            '        sql = "update Acad_Additional_Provider_Info " & _
            '            "set UAI=@UAI, Funding_Agreement_Type=@Funding_Agreement_Type, Federation=@Federation, Multi_Sponsor=@Multi_Sponsor, Multi_Sponsor_Name=@Multi_Sponsor_Name, Growing_School=@Growing_School, Funding_ID=@Funding_ID " & _
            '            "where UAI = @Original_UAI"

            '        Dim checkval As Integer = 0

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@UAI", UAIBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Funding_Agreement_Type", AcadFundingTypeBox.SelectedIndex))

            '        If FederationCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Federation", checkval))

            '        If MultiSponsorCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Multi_Sponsor", checkval))
            '        p.Add(New SqlClient.SqlParameter("@Multi_Sponsor_Name", MultiSponsorNameBox.Text))

            '        If GrowingSchoolCheck.Checked = True Then checkval = 1 Else checkval = 0
            '        p.Add(New SqlClient.SqlParameter("@Growing_School", checkval))

            '        p.Add(New SqlClient.SqlParameter("@Funding_ID", FundingMethodBox.SelectedValue))

            '        p.Add(New SqlClient.SqlParameter("@Original_UAI", OriginalUAI))

            '        RunNonQueryParams(sql, p)

            '    End If

            'End If


            'Dim r As DataRowView

            ''Relationships
            'RelationshipDV.RowFilter = ""
            'For Each r In RelationshipDV

            '    If r("Mode") = "D" Then
            '        'delete
            '        sql = "delete from Relationships " & _
            '          "where RID = @RID"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@RID", r("RID")))
            '        RunNonQueryParams(sql, p)
            '    End If

            '    If r("Mode") = "A" Then
            '        'insert
            '        sql = "insert into Relationships " & _
            '        "(RID, LA_Code, UPIN, Lead) " & _
            '        "values(@RID, @LA_Code, @UPIN, @Lead)"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@RID", GenerateRID))
            '        p.Add(New SqlClient.SqlParameter("@LA_Code", r("LA_Code")))
            '        p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Lead", r("Lead")))

            '        RunNonQueryParams(sql, p)
            '    End If

            '    If r("Mode") = "E" Then
            '        'edit
            '        sql = "update Relationships " & _
            '        "set LA_Code=@LA_Code, UPIN=@UPIN, Lead=@Lead " & _
            '        "where RID = @RID"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@RID", r("RID")))
            '        p.Add(New SqlClient.SqlParameter("@LA_Code", r("LA_Code")))
            '        p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Lead", r("Lead")))

            '        RunNonQueryParams(sql, p)
            '    End If

            '    If OriginalUPIN <> UPINBox.Text Then
            '        If r("Mode") = "O" Then
            '            'update original
            '            sql = "update Relationships " & _
            '        "set LA_Code=@LA_Code, UPIN=@UPIN, Lead=@Lead " & _
            '        "where RID = @RID and UPIN=@Original_UPIN"

            '            p.Clear()
            '            p.Add(New SqlClient.SqlParameter("@RID", r("RID")))
            '            p.Add(New SqlClient.SqlParameter("@LA_Code", r("LA_Code")))
            '            p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '            p.Add(New SqlClient.SqlParameter("@Lead", r("Lead")))
            '            p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))

            '            RunNonQueryParams(sql, p)
            '        End If
            '    End If

            'Next


            ''Funding Streams
            ''Update UPIN for the funding streams incase it changed.
            'If OriginalUPIN <> UPINBox.Text Then
            '    sql = "update Provider_Funding_Streams " & _
            '        "set UPIN = @UPIN where UPIN = @Original_UPIN"

            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '    p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))

            '    RunNonQueryParams(sql, p)

            'End If


            'FundingStreamDV.RowFilter = "Checked <> Original_Check"
            'For Each r In FundingStreamDV

            '    If r("Checked") = "X" Then
            '        sql = "insert into Provider_Funding_Streams " & _
            '        "(Period_ID, UPIN, Budget_ID) " & _
            '        "values(@Period_ID, @UPIN, @Budget_ID)"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
            '        p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Budget_ID", r("Budget_ID")))

            '        RunNonQueryParams(sql, p)
            '    Else
            '        sql = "delete from Provider_Funding_Streams " & _
            '            "where Period_ID = @Period_ID and UPIN = @UPIN and Budget_ID = @Budget_ID"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
            '        p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@Budget_ID", r("Budget_ID")))

            '        RunNonQueryParams(sql, p)
            '    End If

            'Next


            'Contract Information
            'delete
            ContractDV.RowFilter = "Mode = 'D'"
            For Each r In ContractDV
                sql = "delete from CCM_Provider_Contract_Numbers " & _
                    "where period_ID = @Period_ID and UPIN = @Original_UPIN and CCM_Funding_Stream=@CCM_Funding_Stream"

                p.Clear()
                p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
                p.Add(New SqlClient.SqlParameter("@CCM_Funding_Stream", r("CCM_Funding_Stream")))
                p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))

                RunNonQueryParams(sql, p)
            Next

            'insert
            ContractDV.RowFilter = "Mode = 'A'"
            For Each r In ContractDV
                sql = "insert into CCM_Provider_Contract_Numbers " & _
                    "(Period_ID, UPIN, CCM_Funding_Stream, Contract_Number, Parent_Contract_Number) " & _
                    "values(@Period_ID, @UPIN, @CCM_Funding_Stream, @Contract_Number, @Parent_Contract_Number)"

                p.Clear()
                p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
                p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
                p.Add(New SqlClient.SqlParameter("@CCM_Funding_Stream", r("CCM_Funding_Stream")))
                p.Add(New SqlClient.SqlParameter("@Contract_Number", r("Contract_Number")))

                Dim pc As Object

                If Not IsDBNull(r("Parent_Contract_Number")) Then
                    If Trim(r("Parent_Contract_Number")) = "" Then
                        pc = DBNull.Value
                    Else
                        pc = r("Parent_Contract_Number")
                    End If
                Else
                    pc = DBNull.Value
                End If

                p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", pc))

                RunNonQueryParams(sql, p)
            Next

            'Edit
            ContractDV.RowFilter = "Mode = 'E'"
            For Each r In ContractDV
                sql = "update CCM_Provider_Contract_Numbers " & _
                    "set UPIN=@UPIN, Contract_Number=@Contract_Number, Parent_Contract_Number=@Parent_Contract_Number " & _
                    "where period_ID = @Period_ID and UPIN = @Original_UPIN and CCM_Funding_Stream=@CCM_Funding_Stream"

                p.Clear()
                p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
                p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
                p.Add(New SqlClient.SqlParameter("@CCM_Funding_Stream", r("CCM_Funding_Stream")))
                p.Add(New SqlClient.SqlParameter("@Contract_Number", r("Contract_Number")))

                Dim pc As Object

                If Not IsDBNull(r("Parent_Contract_Number")) Then
                    If Trim(r("Parent_Contract_Number")) = "" Then
                        pc = DBNull.Value
                    Else
                        pc = r("Parent_Contract_Number")
                    End If
                Else
                    pc = DBNull.Value
                End If

                p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", pc))
                p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))

                RunNonQueryParams(sql, p)
            Next

            


            'If OriginalUPIN <> UPINBox.Text Then
            '    ContractDV.RowFilter = "Mode = 'O'"
            '    For Each r In ContractDV
            '        sql = "update CCM_Provider_Contract_Numbers " & _
            '            "UPIN=@UPIN, Contract_Number=@Contract_Number, Parent_Contract_Number=@Parent_Contract_Number " & _
            '            "where period_ID = @Period_ID and UPIN = @Original_UPIN and CCM_Funding_Stream=@CCM_Funding_Stream"

            '        p.Clear()
            '        p.Add(New SqlClient.SqlParameter("@Period_ID", r("Period_ID")))
            '        p.Add(New SqlClient.SqlParameter("@UPIN", UPINBox.Text))
            '        p.Add(New SqlClient.SqlParameter("@CCM_Funding_Stream", r("CCM_Funding_Stream")))
            '        p.Add(New SqlClient.SqlParameter("@Contract_Number", r("Contract_Number")))

            '        Dim pc As Object

            '        If Not IsDBNull(r("Parent_Contract_Number")) Then
            '            If Trim(r("Parent_Contract_Number")) = "" Then
            '                pc = DBNull.Value
            '            Else
            '                pc = r("Parent_Contract_Number")
            '            End If
            '        Else
            '            pc = DBNull.Value
            '        End If

            '        p.Add(New SqlClient.SqlParameter("@Parent_Contract_Number", pc))
            '        p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))

            '        RunNonQueryParams(sql, p)
            '    Next
            'End If

            'pre's
            'delete
            'PreDV.RowFilter = "Mode = 'D'"
            'For Each r In PreDV
            '    sql = "delete from Predecessor_Providers where UPIN = @Original_UPIN and Pre_UPIN = @Pre_UPIN"
            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@Original_UPIN", OriginalUPIN))
            '    p.Add(New SqlClient.SqlParameter("@Pre_UPIN", r("Pre_UPIN")))

            '    RunNonQueryParams(sql, p)
            'Next

            ''insert
            'PreDV.RowFilter = "Mode = 'A'"
            'For Each r In PreDV
            '    sql = "insert into Predecessor_Providers (UPIN, Pre_UPIN) values(@UPIN, @Pre_UPIN)"
            '    p.Clear()
            '    p.Add(New SqlClient.SqlParameter("@UPIN", ProviderBox.zSelectedProvider.UPIN))
            '    p.Add(New SqlClient.SqlParameter("@Pre_UPIN", r("Pre_UPIN")))

            '    RunNonQueryParams(sql, p)
            'Next

            
            MainPanel.Visible = False
            MessageBox.Show("The provider has been updated", "Updated provider", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        SaveAll()

    End Sub

    Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteToolStripMenuItem.Click
        DeleteProvider()

    End Sub

    Private Sub DeleteProvider()
        If ProviderBox.zSelectedProvider.RID = "" Then Exit Sub

        If MessageBox.Show("Are you sure you want to delete " & ProviderBox.zSelectedProvider.ProviderName & "?", "Delete Provider?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim sql As String
        'delete provider
        sql = "delete from Providers where upin = " & ProviderBox.zSelectedProvider.UPIN
        RunNonQuery(sql)

        'relationships
        sql = "delete from relationships where upin = " & ProviderBox.zSelectedProvider.UPIN
        RunNonQuery(sql)

        'funding streams
        sql = "delete from Provider_Funding_Streams where upin = " & ProviderBox.zSelectedProvider.UPIN
        RunNonQuery(sql)

        'ccm_contracts
        sql = "delete from CCM_provider_Contract_Numbers where upin = " & ProviderBox.zSelectedProvider.UPIN
        RunNonQuery(sql)

        'Acad
        sql = "delete from Acad_Additional_Provider_Info where UAI = '" & ProviderBox.zSelectedProvider.UAI & "'"
        RunNonQuery(sql)

        'Pre
        sql = "delete from Predecessor_Providers where UPIN = '" & ProviderBox.zSelectedProvider.UPIN & "'"
        RunNonQuery(sql)
        MainPanel.Visible = False
        ProviderBox.ClearSelected()
        ProviderBox.zConnectionString = StoreConnect


    End Sub

    Private Sub ProviderMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ProviderMenu.Opening

        If ProviderBox.zSelectedProvider.RID = "" Then
            DeleteToolStripMenuItem.Visible = False
        Else
            DeleteToolStripMenuItem.Visible = True
        End If



    End Sub

    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
        ProviderBox.ClearSelected()
        ProviderBox.TheTextBox.Text = ""
        MainPanel.Visible = False

    End Sub

    Private Sub ConvertAcademy()
        With Acad_ConverterWizard
            .ShowDialog()
        End With
    End Sub

    Private Sub ConvertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConvertButton.Click

        If TypeBox.SelectedValue.ToString <> "Acade" Then
            MessageBox.Show("This is not an academy", "Not an academy", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("Before the conversion process can start, any changes made to the provider information needs to be saved.  Do you want to continue?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        If ValidateAll() Then
            SaveAll()

            ConvertAcademy()
        End If


    End Sub

    Private Sub TabControl1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles TabControl1.SelectedIndexChanged

        If TabControl1.SelectedIndex = 2 Then
            Button11.Enabled = True
        Else
            Button11.Enabled = False

        End If

    End Sub
End Class